/**
 * 客户端本地SQLite数据库管理
 */
let Database;
try {
  Database = require('better-sqlite3');
} catch (error) {
  console.error('❌ 无法加载 better-sqlite3:', error.message);
  Database = null;
}

const path = require('path');
const { app } = require('electron');
const logger = require('./logger');

class LocalDatabase {
  constructor() {
    this.db = null;
    this.dbPath = path.join(app.getPath('userData'), 'filesync.db');
  }

  /**
   * 初始化数据库
   */
  initialize() {
    try {
      // 检查 better-sqlite3 是否加载成功
      if (!Database) {
        throw new Error('better-sqlite3 模块未加载，可能需要运行 npm run rebuild');
      }

      // 如果已经初始化，直接返回
      if (this.db) {
        logger.info('数据库已经初始化');
        return;
      }

      logger.info(`准备初始化数据库: ${this.dbPath}`);
      
      // 创建数据库实例
      this.db = new Database(this.dbPath, { 
        verbose: (msg) => logger.debug(`[SQLite] ${msg}`)
      });
      
      logger.info('✅ 数据库实例创建成功');
      
      // 启用外键约束
      this.db.pragma('foreign_keys = ON');
      logger.info('✅ 外键约束已启用');
      
      // 创建表结构
      this.createTables();
      logger.info('✅ 数据库表创建成功');
      
      logger.info(`✅ 数据库初始化完成: ${this.dbPath}`);
    } catch (error) {
      logger.error('❌ 数据库初始化失败:', error.message);
      logger.error('错误堆栈:', error.stack);
      this.db = null;
      throw error;
    }
  }

  /**
   * 检查数据库是否已初始化
   */
  isInitialized() {
    return this.db !== null;
  }

  /**
   * 创建数据库表
   */
  createTables() {
    // 传输任务表
    this.db.exec(`
      CREATE TABLE IF NOT EXISTS transfer_tasks (
        id INTEGER PRIMARY KEY,
        task_id INTEGER NOT NULL,
        type TEXT NOT NULL,
        file_name TEXT NOT NULL,
        local_path TEXT,
        remote_path TEXT,
        file_id TEXT,
        file_size INTEGER DEFAULT 0,
        transferred INTEGER DEFAULT 0,
        progress REAL DEFAULT 0,
        speed REAL DEFAULT 0,
        status TEXT NOT NULL,
        error TEXT,
        start_time INTEGER,
        end_time INTEGER,
        created_at INTEGER NOT NULL,
        updated_at INTEGER NOT NULL,
        UNIQUE(task_id)
      );
    `);

    // 创建索引
    this.db.exec(`
      CREATE INDEX IF NOT EXISTS idx_transfer_tasks_status ON transfer_tasks(status);
      CREATE INDEX IF NOT EXISTS idx_transfer_tasks_created_at ON transfer_tasks(created_at);
      CREATE INDEX IF NOT EXISTS idx_transfer_tasks_type ON transfer_tasks(type);
    `);

    // 应用配置表
    this.db.exec(`
      CREATE TABLE IF NOT EXISTS app_config (
        key TEXT PRIMARY KEY,
        value TEXT NOT NULL,
        updated_at INTEGER NOT NULL
      );
    `);

    // 通知表
    this.db.exec(`
      CREATE TABLE IF NOT EXISTS notifications (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        message TEXT NOT NULL,
        type TEXT DEFAULT 'info',
        is_read INTEGER DEFAULT 0,
        created_at INTEGER NOT NULL
      );
    `);

    // 创建通知索引
    this.db.exec(`
      CREATE INDEX IF NOT EXISTS idx_notifications_created_at ON notifications(created_at);
      CREATE INDEX IF NOT EXISTS idx_notifications_is_read ON notifications(is_read);
    `);

    logger.info('数据库表创建完成');
  }

  // ==================== 传输任务操作 ====================

  /**
   * 保存传输任务
   */
  saveTransferTask(task) {
    const stmt = this.db.prepare(`
      INSERT OR REPLACE INTO transfer_tasks (
        task_id, type, file_name, local_path, remote_path, file_id,
        file_size, transferred, progress, speed, status, error,
        start_time, end_time, created_at, updated_at
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `);

    const now = Date.now();
    stmt.run(
      task.id,
      task.type,
      task.fileName,
      task.localPath || null,
      task.remotePath || null,
      task.fileId || null,
      task.fileSize || 0,
      task.transferred || 0,
      task.progress || 0,
      task.speed || 0,
      task.status,
      task.error || null,
      task.startTime || null,
      task.endTime || null,
      task.createdAt || now,
      now
    );
  }

  /**
   * 批量保存传输任务
   */
  saveTransferTasks(tasks) {
    if (!this.db) {
      logger.error('数据库未初始化，无法保存任务');
      return;
    }

    if (!tasks || tasks.length === 0) {
      return;
    }

    const saveMany = this.db.transaction((tasks) => {
      for (const task of tasks) {
        this.saveTransferTask(task);
      }
    });

    saveMany(tasks);
    logger.info(`批量保存 ${tasks.length} 个传输任务`);
  }

  /**
   * 获取所有传输任务
   */
  getAllTransferTasks() {
    if (!this.db) {
      logger.error('数据库未初始化，无法获取任务');
      return [];
    }

    const stmt = this.db.prepare(`
      SELECT * FROM transfer_tasks ORDER BY created_at DESC
    `);
    const rows = stmt.all();
    
    return rows.map(this.rowToTask);
  }

  /**
   * 根据状态获取任务
   */
  getTransferTasksByStatus(status) {
    const stmt = this.db.prepare(`
      SELECT * FROM transfer_tasks WHERE status = ? ORDER BY created_at DESC
    `);
    const rows = stmt.all(status);
    
    return rows.map(this.rowToTask);
  }

  /**
   * 获取单个任务
   */
  getTransferTask(taskId) {
    const stmt = this.db.prepare(`
      SELECT * FROM transfer_tasks WHERE task_id = ?
    `);
    const row = stmt.get(taskId);
    
    return row ? this.rowToTask(row) : null;
  }

  /**
   * 删除已完成的任务
   */
  clearCompletedTasks() {
    const stmt = this.db.prepare(`
      DELETE FROM transfer_tasks WHERE status = 'completed'
    `);
    const result = stmt.run();
    
    logger.info(`从数据库删除 ${result.changes} 个已完成任务`);
    return result.changes;
  }

  /**
   * 删除已取消的任务
   */
  clearCancelledTasks() {
    const stmt = this.db.prepare(`
      DELETE FROM transfer_tasks WHERE status = 'cancelled'
    `);
    const result = stmt.run();
    
    logger.info(`从数据库删除 ${result.changes} 个已取消任务`);
    return result.changes;
  }

  /**
   * 删除所有任务
   */
  clearAllTasks() {
    const stmt = this.db.prepare(`DELETE FROM transfer_tasks`);
    const result = stmt.run();
    
    logger.info(`从数据库删除所有 ${result.changes} 个任务`);
    return result.changes;
  }

  /**
   * 更新任务状态
   */
  updateTaskStatus(taskId, status, error = null) {
    const stmt = this.db.prepare(`
      UPDATE transfer_tasks 
      SET status = ?, error = ?, updated_at = ?
      WHERE task_id = ?
    `);
    
    stmt.run(status, error, Date.now(), taskId);
  }

  /**
   * 更新任务进度
   */
  updateTaskProgress(taskId, transferred, progress, speed) {
    const stmt = this.db.prepare(`
      UPDATE transfer_tasks 
      SET transferred = ?, progress = ?, speed = ?, updated_at = ?
      WHERE task_id = ?
    `);
    
    stmt.run(transferred, progress, speed, Date.now(), taskId);
  }

  /**
   * 数据库行转换为任务对象
   */
  rowToTask(row) {
    return {
      id: row.task_id,
      type: row.type,
      fileName: row.file_name,
      localPath: row.local_path,
      remotePath: row.remote_path,
      fileId: row.file_id,
      fileSize: row.file_size,
      transferred: row.transferred,
      progress: row.progress,
      speed: row.speed,
      status: row.status,
      error: row.error,
      startTime: row.start_time,
      endTime: row.end_time,
      createdAt: row.created_at,
      updatedAt: row.updated_at
    };
  }

  // ==================== 配置操作 ====================

  /**
   * 获取配置
   */
  getConfig(key) {
    const stmt = this.db.prepare(`
      SELECT value FROM app_config WHERE key = ?
    `);
    const row = stmt.get(key);
    
    if (!row) return null;
    
    try {
      return JSON.parse(row.value);
    } catch (error) {
      return row.value;
    }
  }

  /**
   * 设置配置
   */
  setConfig(key, value) {
    const stmt = this.db.prepare(`
      INSERT OR REPLACE INTO app_config (key, value, updated_at)
      VALUES (?, ?, ?)
    `);
    
    const valueStr = typeof value === 'object' ? JSON.stringify(value) : String(value);
    stmt.run(key, valueStr, Date.now());
  }

  /**
   * 删除配置
   */
  deleteConfig(key) {
    const stmt = this.db.prepare(`DELETE FROM app_config WHERE key = ?`);
    stmt.run(key);
  }

  // ==================== 通知操作 ====================

  /**
   * 添加通知
   */
  addNotification(title, message, type = 'info') {
    const stmt = this.db.prepare(`
      INSERT INTO notifications (title, message, type, is_read, created_at)
      VALUES (?, ?, ?, 0, ?)
    `);
    
    const result = stmt.run(title, message, type, Date.now());
    return result.lastInsertRowid;
  }

  /**
   * 获取所有通知
   */
  getAllNotifications(limit = 100) {
    const stmt = this.db.prepare(`
      SELECT * FROM notifications 
      ORDER BY created_at DESC 
      LIMIT ?
    `);
    
    return stmt.all(limit);
  }

  /**
   * 获取未读通知数量
   */
  getUnreadNotificationCount() {
    const stmt = this.db.prepare(`
      SELECT COUNT(*) as count FROM notifications WHERE is_read = 0
    `);
    const row = stmt.get();
    
    return row.count;
  }

  /**
   * 标记通知为已读
   */
  markNotificationAsRead(id) {
    const stmt = this.db.prepare(`
      UPDATE notifications SET is_read = 1 WHERE id = ?
    `);
    stmt.run(id);
  }

  /**
   * 标记所有通知为已读
   */
  markAllNotificationsAsRead() {
    const stmt = this.db.prepare(`
      UPDATE notifications SET is_read = 1 WHERE is_read = 0
    `);
    const result = stmt.run();
    
    return result.changes;
  }

  /**
   * 删除已读通知
   */
  deleteReadNotifications() {
    const stmt = this.db.prepare(`
      DELETE FROM notifications WHERE is_read = 1
    `);
    const result = stmt.run();
    
    logger.info(`删除 ${result.changes} 条已读通知`);
    return result.changes;
  }

  /**
   * 清空所有通知
   */
  clearAllNotifications() {
    const stmt = this.db.prepare(`DELETE FROM notifications`);
    const result = stmt.run();
    
    return result.changes;
  }

  // ==================== 统计信息 ====================

  /**
   * 获取任务统计信息
   */
  getTaskStats() {
    const stmt = this.db.prepare(`
      SELECT 
        status,
        COUNT(*) as count,
        SUM(file_size) as total_size
      FROM transfer_tasks
      GROUP BY status
    `);
    
    const rows = stmt.all();
    const stats = {
      total: 0,
      active: 0,
      pending: 0,
      completed: 0,
      failed: 0,
      paused: 0,
      cancelled: 0,
      totalSize: 0
    };
    
    rows.forEach(row => {
      stats.total += row.count;
      stats[row.status] = row.count;
      stats.totalSize += row.total_size || 0;
    });
    
    return stats;
  }

  // ==================== 数据库维护 ====================

  /**
   * 优化数据库
   */
  optimize() {
    this.db.exec('VACUUM');
    this.db.exec('ANALYZE');
    logger.info('数据库优化完成');
  }

  /**
   * 备份数据库
   */
  backup(backupPath) {
    try {
      this.db.backup(backupPath);
      logger.info(`数据库备份成功: ${backupPath}`);
      return true;
    } catch (error) {
      logger.error('数据库备份失败:', error);
      return false;
    }
  }

  /**
   * 关闭数据库
   */
  close() {
    if (this.db) {
      this.db.close();
      logger.info('数据库已关闭');
    }
  }
}

// 单例模式
let instance = null;

module.exports = {
  getInstance: () => {
    if (!instance) {
      instance = new LocalDatabase();
    }
    return instance;
  }
};
